﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using Lamex.Library;
using Lamex.Base;
using Lamex.Common;
using System.Data.SqlClient;
using System.IO;
using System.Collections;
using Lamex.Library.CommonClass;

namespace Lamex.mTools.UI
{
    public partial class frmTL_Image_Batch_Process : frmBaseChild
    {
        IWriteSQLConfigValue cfgNormal = null;
        string connstr = "";
        const  string DEF_SQL_CONNECTION = @"Data Source={0},1433;Initial Catalog={1};User ID={2};Password ={3};Persist Security Info=True;Connection Timeout=10";/// <summary>
        public frmTL_Image_Batch_Process()
        {
            InitializeComponent();
            this.Load += new EventHandler(frmTL_Image_Batch_Process_Load);
        }
        private void frmTL_Image_Batch_Process_Load(object sender,EventArgs e)
        {
            cfgNormal = new IniFileWriter(Application.StartupPath + SqlConfiguration.INI_CFG_PATH);
            DataTable dt= BaseBuildSql.GetDataTableBySql("SELECT NAME FROM MASTER.DBO.SYSDATABASES ORDER BY NAME");
            foreach(DataRow d in dt.Rows)
            {
                comboBoxDataBase.Items.Add(d["NAME"]);
            }
            comboBoxDataBase.Text = "LamexMESManager";
        }
        private void comboBoxDataBase_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(comboBoxDataBase.Text))
            {
                comboBoxKeyField.Items.Clear();
                comboBoxImageField.Items.Clear();
                comboBoxTableName.Items.Clear();
                connstr = string.Format(DEF_SQL_CONNECTION, cfgNormal.ServerName, comboBoxDataBase.Text, cfgNormal.UserName, cfgNormal.Password);
                DataTable dt = GetDataTableBySql("select NAME from sys.tables order by name", connstr);
                foreach (DataRow d in dt.Rows)
                {
                    comboBoxTableName.Items.Add(d["NAME"]);
                }
                comboBoxTableName.SelectedIndex = 1;
            } 
        }

        private void comboBoxTableName_SelectedIndexChanged(object sender, EventArgs e)
        {
            if(!string.IsNullOrEmpty(comboBoxTableName.Text))
            {
                comboBoxKeyField.Items.Clear();
                comboBoxKeyField.Items.Clear();
                DataTable dtKey = GetDataTableBySql("SELECT NAME FROM syscolumns WHERE id=Object_Id('"+ comboBoxTableName.Text + "') and colid IN(SELECT keyno from sysindexkeys WHERE id=Object_Id('" + comboBoxTableName.Text + "'))", connstr);
                DataTable dtField = GetDataTableBySql(@"SELECT 
                        TableName = OBJECT_NAME(c.object_id),
                        ColumnsName = c.name,
                        Description = ex.value,
                        ColumnType = t.name,
                        Length = c.max_length
                        FROM
                        sys.columns c
                        LEFT OUTER JOIN
                        sys.extended_properties ex
                        ON
                        ex.major_id = c.object_id
                        AND ex.minor_id = c.column_id
                        AND ex.name = 'MS_Description'
                        left outer join
                        systypes t
                        on c.system_type_id = t.xtype
                        WHERE
                        OBJECTPROPERTY(c.object_id, 'IsMsShipped') = 0
                        AND OBJECT_NAME(c.object_id) = '"+ comboBoxTableName.Text + "' and t.name != 'sysname' and t.name = 'image'", connstr);
                if (dtKey.Rows.Count != 0)
                {
                    comboBoxKeyField.Text = dtKey.Rows[0]["NAME"].ToString();
                    
                    //foreach(DataRow d in dtList.Rows)
                    //{
                    //    //string[] Names =dtList.Rows[0]["NAME"].ToString().Split('_');
                    //    comboBoxKeyField.Items.Add(d[dtKey.Rows[0]["NAME"].ToString()]);
                    //    comboBoxKeyField.Tag = dtKey.Rows[0]["NAME"].ToString();
                    //}
                    //comboBoxKeyField.SelectedIndex = 1;
                }
                if (dtField.Rows.Count != 0)
                {
                    foreach (DataRow d in dtField.Rows)
                    {
                        comboBoxImageField.Items.Add(d["ColumnsName"]);
                    }
                    comboBoxImageField.SelectedIndex = 0;
                }
            }
            
            
        }
        public static DataTable GetDataTableBySql(string sql, string url)
        {
            try
            {
                string sConn = url;
                SqlConnection con = new SqlConnection(sConn);
                con.Open();
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = sql;
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    CloseConnection(con);
                    return ds.Tables[0];
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        private static int ExecuteNonQuery(string strSql, string url, PictrueList p)
        {
            string sConn = url;

            using (SqlConnection conn = new SqlConnection(sConn))
            {
                using (SqlCommand cmd = new SqlCommand(strSql, conn))
                {
                    try
                    {
                        conn.Open();
                        
                        SqlParameter sqlParameter = new SqlParameter(p.Name, SqlDbType.Image);
                        sqlParameter.Value = p.bytes;
                        cmd.Parameters.Add(sqlParameter);
                        
                        int rows = cmd.ExecuteNonQuery();
                        conn.Close();
                        return rows;
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }
                }
            }
        }
        private static void CloseConnection(SqlConnection connection)
        {
            if (connection == null) return;
            if (connection.State != ConnectionState.Closed) connection.Close();
        }

        private void buttonSelectedPath_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog f = new FolderBrowserDialog();
            if (f.ShowDialog() == DialogResult.OK)
            {
                txtPath.Text = f.SelectedPath;
            }
        }

        private void buttonImageImport_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtPath.Text))
            {
                XtraMessageBox.Show("路径不能为空!");
                return;
            }
            if (string.IsNullOrEmpty(comboBoxImageField.Text))
            {
                XtraMessageBox.Show("图片字段不能为空!");
                return;
            }
            if (string.IsNullOrEmpty(comboBoxKeyField.Text))
            {
                XtraMessageBox.Show("主键不能为空!");
                return;
            }
            
            List<string> list = new  List<string>();
            List<string> tempList = new List<string>();
            List<Lamex.Library.CommonClass.PictrueList> picList = new List<Library.CommonClass.PictrueList>();
            list.Add(".BMP");
            list.Add(".JPG");
            list.Add(".GIF");
            list.Add(".PNG");
            string filename = "";

            DataTable dtList = GetDataTableBySql("SELECT " + comboBoxKeyField.Text + " FROM " + comboBoxTableName.Text, connstr);

            DirectoryInfo dir = new DirectoryInfo(txtPath.Text);
            FileInfo[] file = dir.GetFiles();

            DirectoryInfo[] dii = dir.GetDirectories();//如需遍历子文件夹时需要使用
            foreach (FileInfo f in file)
            {
                filename = f.FullName;
                string suffix = f.Extension.ToUpper();
                if (string.IsNullOrEmpty(suffix))
                    continue;
                string s = list.Where(p => p.Contains(suffix)).FirstOrDefault();
                if (!string.IsNullOrEmpty(s))
                {
                    Lamex.Library.CommonClass.PictrueList model = new Library.CommonClass.PictrueList();
                    model.Name =  System.IO.Path.GetFileNameWithoutExtension(f.Name); 
                    model.bytes = ConvertAllHelper.ImageToBytes(f.FullName);
                    picList.Add(model);
                }
                     
            }

            
            foreach (PictrueList p in picList)
            {
                DataRow dr = dtList.Select(comboBoxKeyField.Text+"='"+ p.Name + "'").FirstOrDefault();
                if (dr!=null)
                {
                    string sql = "";
                    string strFiled = "";
                    string sqlWhere = " and "+ comboBoxKeyField.Text + "='" + p.Name + "'";
                    PictrueList model = new PictrueList();
                    strFiled += string.Format("{0}={1},", comboBoxImageField.Text, "@" + comboBoxImageField.Text);
                    model.Name = "@" + comboBoxImageField.Text;
                    model.bytes = p.bytes;
                    strFiled = strFiled.Trim(',');
                    sql = string.Format("UPDATE {0} set {1} WHERE 1=1 {2}", comboBoxTableName.Text, strFiled, sqlWhere);
                    ExecuteNonQuery(sql, connstr, model);
                }
                    
            }
            

            XtraMessageBox.Show("成功导入！");
            
        }
        
    }
}